
import pymysql
import pymssql
from DBUtils.PooledDB import PooledDB #1.3版本
from pymysql.cursors import DictCursor
from pymysql.constants import CLIENT


class TDBUtils:
    def __init__(self):
        pass
    @staticmethod
    def getMssqlPool(  host , port , uid , pwd , db=""):
        status = 0
        res = None
        try:
            res = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping=1,
                blocking=True,
                host=host,
                port=port,
                database=db,
                user=uid,
                password=pwd,
                autocommit=True,
            )
            status = 1
        except Exception as er:
            print(er)
        return status , res
    @staticmethod
    def getMysqlPool(  host , port , uid , pwd , db=""):
        status = 0
        res = None
        try:
            res = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=host,
                port=port,
                database=db,
                user=uid,
                password=pwd,
                autocommit=True,
                cursorclass=DictCursor,
                client_flag=CLIENT.MULTI_STATEMENTS
            )
            status = 1
        except Exception as er:
            print(er)
        return status , res
    @staticmethod
    def getPool(  host , port , uid , pwd  ,db="", dbType="mysql" ):
        status = 0
        res = None
        try:
            if dbType == "mysql" :
                status , res = TDBUtils.getMysqlPool(host=host, port=port,uid=uid,pwd=pwd,db=db)
            elif dbType == "mssql" :
                status , res = TDBUtils.getMssqlPool(host=host, port=port,uid=uid,pwd=pwd,db=db)
        except Exception as er:
            print(er)
        return status , res
    @staticmethod
    def query(  pool , sql , params , dbType="mysql" ):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if pool!=None :
                conn = pool.connection()
                try:
                    cursor = None
                    if dbType == 'mysql':
                        conn.cursorclass = pymysql.cursors.DictCursor
                        cursor = conn.cursor()
                    elif dbType=="mssql" :
                        cursor = conn.cursor(as_dict=True)

                    if params==None or len(params.keys())==0:
                        cursor.execute(sql)
                    else:
                        cursor.execute(sql, params)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        # queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            # queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) > 0:
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] = {
                            "rowNum": cursor.rownumber,
                            "rowCount": cursor.rowcount,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as erquery:
                    print(erquery)
        except Exception as er:
            print(er)
        return queryRes
